/*
select * from ts_billset
drop table tb_hr_entry
drop table tb_hr_entrydtl
drop table tb_hr_spearation
drop table tb_hr_spearationdtl
drop table tb_hr_Jobtransfer
drop table tb_hr_Jobtransferdtl
drop table tb_hr_Salary
drop table tb_hr_Salarydtl
drop table tb_hr_Contract

*/
CREATE TABLE [dbo].[tb_hr_entry](
	[bid] int NOT NULL,
	[bdate] datetime NOT NULL,
	[bcode] varchar(40) NOT NULL,
	[bstate] int NOT NULL,
	[applyfilid] int NOT NULL,
	[deptid] int NULL,
	[duty] varchar(40) NULL,
	[post] varchar(40) NULL,
	[applydate] datetime NULL,
	[jobtype] varchar(40) NULL,
	[empid] int NULL,
	[name] varchar(60) NOT NULL,
	[sex] int NULL,
	[birthday] datetime NULL,
	[rootaddress] varchar(200) NULL,
	[registeraddress] varchar(200) NULL,
	[nation] varchar(40) NULL,
	[polity] varchar(40) NULL,
	[marriage] varchar(16) NULL,
	[joinworktime] datetime NULL,
	[idcard] varchar(40) NULL,
	[address] varchar(200) NULL,
	[culture] varchar(40) NULL,
	[specialty] varchar(60) NULL,
	[titlepost] varchar(40) NULL,
	[academy] varchar(60) NULL,
	[strongpoint] varchar(200) NULL,
	[foreignlanguage] varchar(40) NULL,
	[grade] varchar(40) NULL,
	[telephone] varchar(40) NULL,
	[email] varchar(100) NULL,
	[remark] varchar(200) NULL,
	[create_by] int NOT NULL,
	[filid] int NOT NULL,
	[explain] varchar(200) NULL,
	[check_by] int NULL,
	[examineresult] bit,
	[examineOpinion] nvarchar(200),
	[examine_by] int,
	[examinedate] datetime,
	[create_time] datetime,
	[update_time] datetime,
	[check_time] datetime,
	[custom1] [nvarchar](100) NULL,
	[custom2] [nvarchar](100) NULL,
	[custom3] [nvarchar](100) NULL,
	[custom4] [nvarchar](100) NULL,
	[custom5] [nvarchar](100) NULL,
	[custom6] [nvarchar](100) NULL,
	[custom7] [nvarchar](100) NULL,
	[custom8] [nvarchar](100) NULL,
	[custom9] [nvarchar](100) NULL,
	[custom10] [nvarchar](100) NULL,
	[custom11] [decimal](22,8) NULL,
	[custom12] [decimal](22,8) NULL,	
	[custom13] [decimal](22,8) NULL,	
	[custom14] [decimal](22,8) NULL,	
    [custom15] [decimal](22,8) NULL,		
 CONSTRAINT [PK_tb_hr_entry] PRIMARY KEY CLUSTERED 
(
	[bid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_tb_hr_entry] UNIQUE NONCLUSTERED 
(
	[bcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_entry]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entry_td_filiale] FOREIGN KEY([filid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_entry] CHECK CONSTRAINT [FK_tb_hr_entry_td_filiale]
GO

ALTER TABLE [dbo].[tb_hr_entry]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entry_td_filiale1] FOREIGN KEY([applyfilid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_entry] CHECK CONSTRAINT [FK_tb_hr_entry_td_filiale1]
GO

ALTER TABLE [dbo].[tb_hr_entry]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entry_td_dept] FOREIGN KEY([deptid])
REFERENCES [dbo].[td_dept] ([ed_deptid])
GO

ALTER TABLE [dbo].[tb_hr_entry] CHECK CONSTRAINT [FK_tb_hr_entry_td_dept]
GO

ALTER TABLE [dbo].[tb_hr_entry]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entry_ts_usersinfo] FOREIGN KEY([create_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_entry] CHECK CONSTRAINT [FK_tb_hr_entry_ts_usersinfo]
GO

ALTER TABLE [dbo].[tb_hr_entry]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entry_ts_usersinfo1] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_entry] CHECK CONSTRAINT [FK_tb_hr_entry_ts_usersinfo1]
GO



/*
CREATE TABLE [dbo].[tb_hr_entrydtl](
	[bid] int NOT NULL,
	[bno] int NOT NULL,
	[examinedate] datetime NOT NULL,
	[examineresult] int NOT NULL,
	[opinion] varchar(200) NOT NULL,
	[check_by] int NOT NULL,
	[duty] varchar(40) NULL,
	[create_time] datetime,
	[update_time] datetime,	
 CONSTRAINT [PK_tb_hr_entrydtl] PRIMARY KEY CLUSTERED 
(
	[bid] ASC,
	[bno] ASC,
	[check_by] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_entrydtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entrydetail_tb_hr_entry] FOREIGN KEY([bid])
REFERENCES [dbo].[tb_hr_entry] ([bid])
GO

ALTER TABLE [dbo].[tb_hr_entrydtl] CHECK CONSTRAINT [FK_tb_hr_entrydetail_tb_hr_entry]
GO

ALTER TABLE [dbo].[tb_hr_entrydtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_entrydetail_ts_usersinfo] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_entrydtl] CHECK CONSTRAINT [FK_tb_hr_entrydetail_ts_usersinfo]
GO
*/


--tb_hr_spearation
CREATE TABLE [dbo].[tb_hr_spearation](
	[bid] int NOT NULL,
	[bdate] datetime NOT NULL,
	[bcode] varchar(40) NOT NULL,
	[bstate] int NOT NULL,
	[empid] int NOT NULL,
	[applyfilid] int NOT NULL,
	[deptid] int NULL,
	[duty] varchar(40) NULL,
	[post] varchar(40) NULL,
	[telephone] varchar(40) NULL,
	[email] varchar(40) NULL,
	[applydate] datetime NOT NULL,
	[spearationdate] datetime NULL,
	[handtakeover] varchar(200) NULL,
	[reason] varchar(128) NOT NULL,
	[handleempid] int NULL,
	[remark] varchar(200) NULL,
	[create_by] int NOT NULL,
	[filid] int NOT NULL,
	[explain] varchar(200) NULL,
	[check_by] int NULL,
	[examineresult] bit,
	[examineOpinion] nvarchar(200),
	[examine_by] int,
	[examinedate] datetime,	
	[create_time] datetime,
	[update_time] datetime,
	[check_time] datetime,	
	[custom1] [nvarchar](100) NULL,
	[custom2] [nvarchar](100) NULL,
	[custom3] [nvarchar](100) NULL,
	[custom4] [nvarchar](100) NULL,
	[custom5] [nvarchar](100) NULL,
	[custom6] [nvarchar](100) NULL,
	[custom7] [nvarchar](100) NULL,
	[custom8] [nvarchar](100) NULL,
	[custom9] [nvarchar](100) NULL,
	[custom10] [nvarchar](100) NULL,
	[custom11] [decimal](22,8) NULL,
	[custom12] [decimal](22,8) NULL,	
	[custom13] [decimal](22,8) NULL,	
	[custom14] [decimal](22,8) NULL,	
    [custom15] [decimal](22,8) NULL,		
 CONSTRAINT [PK_tb_hr_spearation] PRIMARY KEY CLUSTERED 
(
	[bid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_tb_hr_spearation] UNIQUE NONCLUSTERED 
(
	[bcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_spearation]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearation_td_filiale] FOREIGN KEY([applyfilid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_spearation] CHECK CONSTRAINT [FK_tb_hr_spearation_td_filiale]
GO

ALTER TABLE [dbo].[tb_hr_spearation]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearation_td_filiale1] FOREIGN KEY([filid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_spearation] CHECK CONSTRAINT [FK_tb_hr_spearation_td_filiale1]
GO

ALTER TABLE [dbo].[tb_hr_spearation]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearation_td_dept1] FOREIGN KEY([deptid])
REFERENCES [dbo].[td_dept] ([ed_deptid])
GO

ALTER TABLE [dbo].[tb_hr_spearation] CHECK CONSTRAINT [FK_tb_hr_spearation_td_dept1]
GO

ALTER TABLE [dbo].[tb_hr_spearation]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearation_td_empmaster] FOREIGN KEY([handleempid])
REFERENCES [dbo].[td_empmaster] ([em_employeeid])
GO

ALTER TABLE [dbo].[tb_hr_spearation] CHECK CONSTRAINT [FK_tb_hr_spearation_td_empmaster]
GO

ALTER TABLE [dbo].[tb_hr_spearation]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearation_ts_usersinfo] FOREIGN KEY([create_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_spearation] CHECK CONSTRAINT [FK_tb_hr_spearation_ts_usersinfo]
GO

ALTER TABLE [dbo].[tb_hr_spearation]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearation_ts_usersinfo1] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_spearation] CHECK CONSTRAINT [FK_tb_hr_spearation_ts_usersinfo1]
GO



/*
CREATE TABLE [dbo].[tb_hr_spearationdtl](
	[bid] int NOT NULL,
	[bno] int NOT NULL,
	[examinedate] datetime NOT NULL,
	[examineresult] int NOT NULL,
	[opinion] varchar(200) NOT NULL,
	[check_by] int NOT NULL,
	[duty] varchar(40) NULL,
	[create_time] datetime,
	[update_time] datetime,	
 CONSTRAINT [PK_tb_hr_spearationdtl] PRIMARY KEY CLUSTERED 
(
	[bid] ASC,
	[bno] ASC,
	[check_by] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_spearationdtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearationdetail_tb_hr_spearation] FOREIGN KEY([bid])
REFERENCES [dbo].[tb_hr_spearation] ([bid])
GO

ALTER TABLE [dbo].[tb_hr_spearationdtl] CHECK CONSTRAINT [FK_tb_hr_spearationdetail_tb_hr_spearation]
GO

ALTER TABLE [dbo].[tb_hr_spearationdtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_spearationdetail_ts_usersinfo] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_spearationdtl] CHECK CONSTRAINT [FK_tb_hr_spearationdetail_ts_usersinfo]
GO

*/
--tb_hr_Jobtransfer
CREATE TABLE [dbo].[tb_hr_Jobtransfer](
	[bid] int NOT NULL,
	[bdate] datetime NOT NULL,
	[bcode] varchar(40) NOT NULL,
	[bstate] int NOT NULL,
	[empid] int NOT NULL,
	[srcfilid] int NOT NULL,
	[srcdeptid] int NULL,
	[srcduty] varchar(40) NULL,
	[srcpost] varchar(40) NULL,
	[dstfilid] int NOT NULL,
	[dstdeptid] int NULL,
	[dstduty] varchar(40) NULL,
	[dstpost] varchar(40) NULL,
	[handleid] int NULL,
	[placementdate] datetime NULL,
	[reason] varchar(200) NULL,
	[selfappraise] varchar(200) NULL,
	[handtakeover] varchar(200) NULL,
	[filid] int NOT NULL,
	[create_by] int NOT NULL,
	[remark] varchar(200) NULL,
	[explain] varchar(200) NULL,
	[check_by] int NULL,
	[examineresult] bit,
	[examineOpinion] nvarchar(200),
	[examine_by] int,
	[examinedate] datetime,	
	[create_time] datetime,
	[update_time] datetime,
	[check_time] datetime,
	[custom1] [nvarchar](100) NULL,
	[custom2] [nvarchar](100) NULL,
	[custom3] [nvarchar](100) NULL,
	[custom4] [nvarchar](100) NULL,
	[custom5] [nvarchar](100) NULL,
	[custom6] [nvarchar](100) NULL,
	[custom7] [nvarchar](100) NULL,
	[custom8] [nvarchar](100) NULL,
	[custom9] [nvarchar](100) NULL,
	[custom10] [nvarchar](100) NULL,
	[custom11] [decimal](22,8) NULL,
	[custom12] [decimal](22,8) NULL,	
	[custom13] [decimal](22,8) NULL,	
	[custom14] [decimal](22,8) NULL,	
    [custom15] [decimal](22,8) NULL,		
 CONSTRAINT [PK_tb_hr_Jobtransfer] PRIMARY KEY CLUSTERED 
(
	[bid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_tb_hr_Jobtransfer] UNIQUE NONCLUSTERED 
(
	[bcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_filiale] FOREIGN KEY([srcfilid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_filiale]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_filiale1] FOREIGN KEY([dstfilid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_filiale1]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_filiale2] FOREIGN KEY([filid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_filiale2]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_dept] FOREIGN KEY([srcdeptid])
REFERENCES [dbo].[td_dept] ([ed_deptid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_dept]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_dept1] FOREIGN KEY([dstdeptid])
REFERENCES [dbo].[td_dept] ([ed_deptid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_dept1]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_empmaster] FOREIGN KEY([empid])
REFERENCES [dbo].[td_empmaster] ([em_employeeid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_empmaster]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransfer_td_empmaster1] FOREIGN KEY([handleid])
REFERENCES [dbo].[td_empmaster] ([em_employeeid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransfer_td_empmaster1]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransferts_usersinfo] FOREIGN KEY([create_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransferts_usersinfo]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransferts_usersinfo1] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransfer] CHECK CONSTRAINT [FK_tb_hr_Jobtransferts_usersinfo1]
GO



/*
CREATE TABLE [dbo].[tb_hr_Jobtransferdtl](
	[bid] int NOT NULL,
	[bno] int NOT NULL,
	[examinedate] datetime NOT NULL,
	[examineresult] int NOT NULL,
	[opinion] varchar(200) NOT NULL,
	[check_by] int NOT NULL,
	[duty] varchar(40) NULL,
	[create_time] datetime,
	[update_time] datetime,	
 CONSTRAINT [PK_tb_hr_Jobtransferdtl] PRIMARY KEY CLUSTERED 
(
	[bid] ASC,
	[bno] ASC,
	[check_by] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_Jobtransferdtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransferdetail_tb_hr_Jobtransfer] FOREIGN KEY([bid])
REFERENCES [dbo].[tb_hr_Jobtransfer] ([bid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransferdtl] CHECK CONSTRAINT [FK_tb_hr_Jobtransferdetail_tb_hr_Jobtransfer]
GO

ALTER TABLE [dbo].[tb_hr_Jobtransferdtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Jobtransferdetail_ts_usersinfo] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Jobtransferdtl] CHECK CONSTRAINT [FK_tb_hr_Jobtransferdetail_ts_usersinfo]
GO

*/

--tb_hr_Salary
CREATE TABLE [dbo].[tb_hr_Salary](
	[bid] int NOT NULL,
	[bdate] datetime NOT NULL,
	[bcode] varchar(40) NOT NULL,
	[bstate] int NOT NULL,
	[bkind] int NOT NULL,
	[btype] varchar(40) NULL,
	[empid] int NOT NULL,
	[empfilid] int NOT NULL,
	[empdeptid] int NULL,
	[empduty] varchar(40) NULL,
	[emppost] varchar(40) NULL,
	[beforestipend] decimal(18,2) NOT NULL,
	[afterstipend] decimal(18,2) NOT NULL,
	[handleempid] int NULL,
	[reason] varchar(200) NULL,
	[filid] int NOT NULL,
	[create_by] int NOT NULL,
	[explain] varchar(200) NULL,
	[remark] varchar(200) NULL,
	[check_by] int NULL,
	[examineresult] bit,
	[examineOpinion] nvarchar(200),
	[examine_by] int,
	[examinedate] datetime,	
	[create_time] datetime,
	[update_time] datetime,
	[check_time] datetime,
	[custom1] [nvarchar](100) NULL,
	[custom2] [nvarchar](100) NULL,
	[custom3] [nvarchar](100) NULL,
	[custom4] [nvarchar](100) NULL,
	[custom5] [nvarchar](100) NULL,
	[custom6] [nvarchar](100) NULL,
	[custom7] [nvarchar](100) NULL,
	[custom8] [nvarchar](100) NULL,
	[custom9] [nvarchar](100) NULL,
	[custom10] [nvarchar](100) NULL,
	[custom11] [decimal](22,8) NULL,
	[custom12] [decimal](22,8) NULL,	
	[custom13] [decimal](22,8) NULL,	
	[custom14] [decimal](22,8) NULL,	
    [custom15] [decimal](22,8) NULL,		
 CONSTRAINT [PK_tb_hr_Salary] PRIMARY KEY CLUSTERED 
(
	[bid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_tb_hr_Salary] UNIQUE NONCLUSTERED 
(
	[bcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_Salary]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salary_td_filiale] FOREIGN KEY([filid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Salary] CHECK CONSTRAINT [FK_tb_hr_Salary_td_filiale]
GO

ALTER TABLE [dbo].[tb_hr_Salary]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salary_td_filiale1] FOREIGN KEY([empfilid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Salary] CHECK CONSTRAINT [FK_tb_hr_Salary_td_filiale1]
GO

ALTER TABLE [dbo].[tb_hr_Salary]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salary_td_empmaster] FOREIGN KEY([empid])
REFERENCES [dbo].[td_empmaster] ([em_employeeid])
GO

ALTER TABLE [dbo].[tb_hr_Salary] CHECK CONSTRAINT [FK_tb_hr_Salary_td_empmaster]
GO

ALTER TABLE [dbo].[tb_hr_Salary]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salary_td_empmaster1] FOREIGN KEY([handleempid])
REFERENCES [dbo].[td_empmaster] ([em_employeeid])
GO

ALTER TABLE [dbo].[tb_hr_Salary] CHECK CONSTRAINT [FK_tb_hr_Salary_td_empmaster1]
GO

ALTER TABLE [dbo].[tb_hr_Salary]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salary_ts_usersinfo] FOREIGN KEY([create_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Salary] CHECK CONSTRAINT [FK_tb_hr_Salary_ts_usersinfo]
GO

ALTER TABLE [dbo].[tb_hr_Salary]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salary_ts_usersinfo1] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Salary] CHECK CONSTRAINT [FK_tb_hr_Salary_ts_usersinfo1]
GO



/*
CREATE TABLE [dbo].[tb_hr_Salarydtl](
	[bid] int NOT NULL,
	[bno] int NOT NULL,
	[examinedate] datetime NOT NULL,
	[examineresult] int NOT NULL,
	[opinion] varchar(200) NOT NULL,
	[check_by] int NOT NULL,
	[duty] varchar(40) NULL,
	[create_time] datetime,
	[update_time] datetime,	
 CONSTRAINT [PK_tb_hr_Salarydtl] PRIMARY KEY CLUSTERED 
(
	[bid] ASC,
	[bno] ASC,
	[check_by] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_Salarydtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salarydetail_tb_hr_Salary] FOREIGN KEY([bid])
REFERENCES [dbo].[tb_hr_Salary] ([bid])
GO

ALTER TABLE [dbo].[tb_hr_Salarydtl] CHECK CONSTRAINT [FK_tb_hr_Salarydetail_tb_hr_Salary]
GO

ALTER TABLE [dbo].[tb_hr_Salarydtl]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Salarydetail_ts_usersinfo] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Salarydtl] CHECK CONSTRAINT [FK_tb_hr_Salarydetail_ts_usersinfo]
GO
*/

--tb_hr_Contract

CREATE TABLE [dbo].[tb_hr_Contract](
	[bid] int NOT NULL,
	[bdate] datetime NOT NULL,
	[bcode] varchar(40) NOT NULL,
	[bstate] int NOT NULL,
	[bkind] int NOT NULL,
	[jobtype] varchar(40) NULL,
	[contractno] varchar(40) NOT NULL,
	[empid] int NOT NULL,
	[empfilid] int NOT NULL,
	[empdeptid] int NULL,
	[empduty] varchar(40) NULL,
	[emppost] varchar(40) NULL,
	[startdate] datetime NOT NULL,
	[enddate] datetime NOT NULL,
	[contractterm] int NOT NULL,
	[probation] int NULL,
	[probationstipendmonth] decimal(18,2) NULL,
	[probationstipendyear] decimal(18,2) NULL,
	[regularstipendmonth] decimal(18,2) NULL,
	[regularstipendyear] decimal(18,2) NULL,
	[company] varchar(128) NULL,
	[address] varchar(200) NULL,
	[juridicalperson] varchar(128) NULL,
	[comsigndate] datetime NOT NULL,
	[empsigndate] datetime NOT NULL,
	[closed] int NULL,
	[remark] varchar(200) NULL,
	[article] [text] NULL,
	[useexplain] [text] NULL,
	[filid] int NOT NULL,
	[create_by] int NOT NULL,
	[check_by] int NULL,
	[explain] varchar(200) NULL,
	[create_time] datetime,
	[update_time] datetime,
	[check_time] datetime,
	[custom1] [nvarchar](100) NULL,
	[custom2] [nvarchar](100) NULL,
	[custom3] [nvarchar](100) NULL,
	[custom4] [nvarchar](100) NULL,
	[custom5] [nvarchar](100) NULL,
	[custom6] [nvarchar](100) NULL,
	[custom7] [nvarchar](100) NULL,
	[custom8] [nvarchar](100) NULL,
	[custom9] [nvarchar](100) NULL,
	[custom10] [nvarchar](100) NULL,
	[custom11] [decimal](22,8) NULL,
	[custom12] [decimal](22,8) NULL,	
	[custom13] [decimal](22,8) NULL,	
	[custom14] [decimal](22,8) NULL,	
    [custom15] [decimal](22,8) NULL,		
 CONSTRAINT [PK_tb_hr_Contract] PRIMARY KEY CLUSTERED 
(
	[bid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_tb_hr_Contract] UNIQUE NONCLUSTERED 
(
	[bcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_tb_hr_Contract_1] UNIQUE NONCLUSTERED 
(
	[contractno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tb_hr_Contract]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Contract_td_filiale] FOREIGN KEY([empfilid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Contract] CHECK CONSTRAINT [FK_tb_hr_Contract_td_filiale]
GO

ALTER TABLE [dbo].[tb_hr_Contract]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Contract_td_filiale1] FOREIGN KEY([filid])
REFERENCES [dbo].[td_filiale] ([fi_filid])
GO

ALTER TABLE [dbo].[tb_hr_Contract] CHECK CONSTRAINT [FK_tb_hr_Contract_td_filiale1]
GO

ALTER TABLE [dbo].[tb_hr_Contract]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Contract_td_empmaster] FOREIGN KEY([empid])
REFERENCES [dbo].[td_empmaster] ([em_employeeid])
GO

ALTER TABLE [dbo].[tb_hr_Contract] CHECK CONSTRAINT [FK_tb_hr_Contract_td_empmaster]
GO

ALTER TABLE [dbo].[tb_hr_Contract]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Contract_ts_usersinfo] FOREIGN KEY([create_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Contract] CHECK CONSTRAINT [FK_tb_hr_Contract_ts_usersinfo]
GO

ALTER TABLE [dbo].[tb_hr_Contract]  WITH CHECK ADD  CONSTRAINT [FK_tb_hr_Contract_ts_usersinfo1] FOREIGN KEY([check_by])
REFERENCES [dbo].[ts_usersinfo] ([ui_userid])
GO

ALTER TABLE [dbo].[tb_hr_Contract] CHECK CONSTRAINT [FK_tb_hr_Contract_ts_usersinfo1]
GO



